package com.yang.doctorrecruitment.dao;

import com.yang.doctorrecruitment.dto.ReleaseJob;
import com.yang.doctorrecruitment.utils.DBManager;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class JobDAO {
    private static Connection conn;
    public static boolean insertJob(ReleaseJob job){
        conn= DBManager.getconnection();
        ResultSet res=null;
        PreparedStatement statement=null;

        String sql=" insert into job" +
                "(UserID,Job,JobDuty,JobRequire,JobSalary,JobNum,JobAddress,PublishTime,Static) " +
                "values(?,?,?,?,?,?,?,?,?)";
        try{
            statement=conn.prepareStatement(sql);
            statement.setString(1,job.getUserID());
            statement.setString(2,job.getJobName());
            statement.setString(3,job.getJobDuty());
            statement.setString(4,job.getJobRequire());
            statement.setInt(5,job.getJobSalary());
            statement.setInt(6,job.getJobNum());
            statement.setString(7,job.getJobAddress());
            statement.setString(8,job.getPublishTime());
            statement.setString(9,job.getStaticWords());

            int count=statement.executeUpdate();
            if(count>=1){
                return true;
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            DBManager.closeAll(conn,statement,res);
        }
        return false;
    }


    public static List<ReleaseJob> getReleseJob(String UserID){
        conn= DBManager.getconnection();
        ResultSet res=null;
        PreparedStatement statement=null;
        List<ReleaseJob> list=new ArrayList<>();
        String sql="select * from job where UserID=?";
        try{
            statement=conn.prepareStatement(sql);
            statement.setString(1,UserID);
            res=statement.executeQuery();

            while(res.next()){
                ReleaseJob job=new ReleaseJob();
                job.setUserID(UserID);
                job.setJobName(res.getString("Job"));
                job.setJobID(res.getInt("JobID")+"");
                job.setJobNum(res.getInt("JobNum"));
                job.setPublishTime(res.getDate("PublishTime")+"");
                job.setJobSalary(res.getInt("JobSalary"));
                job.setJobAddress(res.getString("JobAddress"));
                job.setJobRequire(res.getString("JobRequire"));
                System.out.println(res.getString("JobRequire")+"------");
                list.add(job);
            }

        }catch (Exception e){
            e.printStackTrace();
        }finally {
            DBManager.closeAll(conn,statement,res);
        }
        return list;
    }

    public static ReleaseJob getReleseJobByJobID(String JobID){
        conn= DBManager.getconnection();
        ResultSet res=null;
        PreparedStatement statement=null;
        List<ReleaseJob> list=new ArrayList<>();
        String sql="select * from job where JobID=?";
        try{
            statement=conn.prepareStatement(sql);
            statement.setString(1,JobID);
            res=statement.executeQuery();

            while(res.next()){
                ReleaseJob job=new ReleaseJob();

                job.setJobName(res.getString("Job"));
                job.setJobID(res.getInt("JobID")+"");
                job.setJobNum(res.getInt("JobNum"));
                job.setPublishTime(res.getDate("PublishTime")+"");
                job.setJobSalary(res.getInt("JobSalary"));
                job.setJobAddress(res.getString("JobAddress"));
                job.setJobRequire(res.getString("JobRequire"));
                job.setUserID(res.getString("UserID"));
                job.setJobDuty(res.getString("JobDuty"));
                return job;
            }

        }catch (Exception e){
            e.printStackTrace();
        }finally {
            DBManager.closeAll(conn,statement,res);
        }
        return null;
    }



    public static boolean updataJob(ReleaseJob job,String JobID){
        conn= DBManager.getconnection();
        ResultSet res=null;
        PreparedStatement statement=null;

        String sql="update job set Job=?,JobDuty=?,JobRequire=?,JobSalary=?,JobNum=?,JobAddress=?,Static=? where JobID=?";
        try{
            statement=conn.prepareStatement(sql);
            statement.setString(1,job.getJobName());
            statement.setString(2,job.getJobDuty());
            statement.setString(3,job.getJobRequire());
            statement.setInt(4,job.getJobSalary());
            statement.setInt(5,job.getJobNum());
            statement.setString(6,job.getJobAddress());
            statement.setString(7,job.getJobName()+job.getJobAddress());
            statement.setString(8,JobID);

            int count=statement.executeUpdate();
            if(count>=1){
                return true;
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            DBManager.closeAll(conn,statement,res);
        }
        return false;
    }

    public static boolean deleteJob(String JobID){
        conn= DBManager.getconnection();
        ResultSet res=null;
        PreparedStatement statement=null;

        String sql="delete from job where JobID=?";
        try{
            statement=conn.prepareStatement(sql);
            statement.setString(1,JobID);


            int count=statement.executeUpdate();
            if(count>=1){
                return true;
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            DBManager.closeAll(conn,statement,res);
        }
        return false;
    }

    public static List<ReleaseJob> searchByJobName(String searchContent){
        conn= DBManager.getconnection();
        ResultSet res=null;
        PreparedStatement statement=null;
        List<ReleaseJob> jobs=new ArrayList<>();
        String sql="select * from  job where Job like CONCAT(?,?,?)";
        try{
            statement=conn.prepareStatement(sql);
            statement.setString(1,"%");
            statement.setString(2,searchContent);
            statement.setString(3,"%");
            res=statement.executeQuery();

           while(res.next()){
               ReleaseJob job=new ReleaseJob();
               job.setJobName(res.getString("Job"));
               job.setJobID(res.getInt("JobID")+"");
               job.setJobNum(res.getInt("JobNum"));
               job.setPublishTime(res.getDate("PublishTime")+"");
               job.setJobSalary(res.getInt("JobSalary"));
               job.setJobAddress(res.getString("JobAddress"));
               job.setJobRequire(res.getString("JobRequire"));
               job.setUserID(res.getString("UserID"));
               jobs.add(job);
           }
           return jobs;
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            DBManager.closeAll(conn,statement,res);
        }
        return null;
    }
    public static List<ReleaseJob> searchByJobAddress(String searchContent){
        conn= DBManager.getconnection();
        ResultSet res=null;
        PreparedStatement statement=null;
        List<ReleaseJob> jobs=new ArrayList<>();
        String sql="select * from  job where JobAddress like CONCAT(?,?,?)";
        try{
            statement=conn.prepareStatement(sql);
            statement.setString(1,"%");
            statement.setString(2,searchContent);
            statement.setString(3,"%");
            res=statement.executeQuery();

            while(res.next()){
                ReleaseJob job=new ReleaseJob();
                job.setJobName(res.getString("Job"));
                job.setJobID(res.getInt("JobID")+"");
                job.setJobNum(res.getInt("JobNum"));
                job.setPublishTime(res.getDate("PublishTime")+"");
                job.setJobSalary(res.getInt("JobSalary"));
                job.setJobAddress(res.getString("JobAddress"));
                job.setJobRequire(res.getString("JobRequire"));
                job.setUserID(res.getString("UserID"));
                jobs.add(job);
            }
            return jobs;
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            DBManager.closeAll(conn,statement,res);
        }
        return null;
    }

    public static List<ReleaseJob> searchByStatic(String searchContent){
        conn= DBManager.getconnection();
        ResultSet res=null;
        PreparedStatement statement=null;
        List<ReleaseJob> jobs=new ArrayList<>();
        String sql="select * from  job where Static like CONCAT(?,?,?)";
        try{
            statement=conn.prepareStatement(sql);
            statement.setString(1,"%");
            statement.setString(2,searchContent);
            statement.setString(3,"%");
            res=statement.executeQuery();

            while(res.next()){
                ReleaseJob job=new ReleaseJob();
                job.setJobName(res.getString("Job"));
                job.setJobID(res.getInt("JobID")+"");
                job.setJobNum(res.getInt("JobNum"));
                job.setPublishTime(res.getDate("PublishTime")+"");
                job.setJobSalary(res.getInt("JobSalary"));
                job.setJobAddress(res.getString("JobAddress"));
                job.setJobRequire(res.getString("JobRequire"));
                job.setUserID(res.getString("UserID"));
                jobs.add(job);
            }
            return jobs;
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            DBManager.closeAll(conn,statement,res);
        }
        return null;
    }

    public static List<ReleaseJob> getAllCollectionByUserID(String UserID){
        conn= DBManager.getconnection();
        ResultSet res=null;
        PreparedStatement statement=null;
        List<ReleaseJob> jobs=new ArrayList<>();
        String sql="select * from job where JobID in (select JobID from collection where UserID=?)";
        try{
            statement=conn.prepareStatement(sql);
            statement.setString(1,UserID);
            res=statement.executeQuery();

            while(res.next()){
                ReleaseJob job=new ReleaseJob();
                job.setJobName(res.getString("Job"));
                job.setJobID(res.getInt("JobID")+"");
                job.setJobNum(res.getInt("JobNum"));
                job.setPublishTime(res.getDate("PublishTime")+"");
                job.setJobSalary(res.getInt("JobSalary"));
                job.setJobAddress(res.getString("JobAddress"));
                job.setJobRequire(res.getString("JobRequire"));
                job.setUserID(res.getString("UserID"));
                jobs.add(job);
            }
            return jobs;
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            DBManager.closeAll(conn,statement,res);
        }
        return null;
    }


    public static boolean deleteCollection(String UserID,String JobID){
        conn= DBManager.getconnection();
        ResultSet res=null;
        PreparedStatement statement=null;

        String sql="delete from collection where UserID=? and JobID=?";
        try{
            statement=conn.prepareStatement(sql);
            statement.setString(1,UserID);
            statement.setString(2,JobID);

            int count=statement.executeUpdate();
            if(count>=1){
                return true;
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            DBManager.closeAll(conn,statement,res);
        }
        return false;
    }



    public static boolean getReleseJobByJobID(ReleaseJob Job){
        conn= DBManager.getconnection();
        ResultSet res=null;
        PreparedStatement statement=null;
        List<ReleaseJob> list=new ArrayList<>();
        String sql="select * from job where JobID=?";
        try{
            statement=conn.prepareStatement(sql);
            statement.setString(1,Job.getJobID());
            res=statement.executeQuery();

            while(res.next()){
                Job.setJobName(res.getString("Job"));
                Job.setJobID(res.getInt("JobID")+"");
                Job.setJobNum(res.getInt("JobNum"));
                Job.setPublishTime(res.getDate("PublishTime")+"");
                Job.setJobSalary(res.getInt("JobSalary"));
                Job.setJobAddress(res.getString("JobAddress"));
                Job.setJobRequire(res.getString("JobRequire"));
                Job.setUserID(res.getString("UserID"));
                Job.setJobDuty(res.getString("JobDuty"));
                return true;
            }

        }catch (Exception e){
            e.printStackTrace();
        }finally {
            DBManager.closeAll(conn,statement,res);
        }
        return false;
    }
}
